﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using DBUtility;
 
namespace SQLServerDAL
{
    /// <summary>
    /// 用户评论系统
    /// </summary>
    public class CommentBase
    {
        private SqlParameter[] Parms;
        private SqlConnection Conn;

        public CommentBase()
        {
            Conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction);
        }

        /// <summary>
        /// 新建评论
        /// </summary>
        public void CreateComment(Model.CommentBase _comment, out string result)
        {
            Parms = new SqlParameter[] { 
					new SqlParameter("@Title", SqlDbType.NVarChar, 512),
					new SqlParameter("@Comments", SqlDbType.NVarChar, 4000),
					new SqlParameter("@Comments1", SqlDbType.NVarChar, 4000),
					new SqlParameter("@Comments2", SqlDbType.NVarChar, 4000),
					new SqlParameter("@Author", SqlDbType.VarChar, 256),
					new SqlParameter("@AuthorId", SqlDbType.UniqueIdentifier),
					new SqlParameter("@GoodCounter", SqlDbType.Int),
					new SqlParameter("@BadCounter", SqlDbType.Int),
					new SqlParameter("@RegardingObjectId", SqlDbType.UniqueIdentifier),
					new SqlParameter("@RegardingObjectIdName", SqlDbType.NVarChar, 1024),
					new SqlParameter("@RegardingObjectTypeCode", SqlDbType.Int), 
					new SqlParameter("@CreatedBy", SqlDbType.UniqueIdentifier), 
					new SqlParameter("@ModifyBy", SqlDbType.UniqueIdentifier),
					new SqlParameter("@StatusCode", SqlDbType.Int),
                    new SqlParameter("@CommentId",SqlDbType.UniqueIdentifier,16,"Output"),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
            };
            Parms[0].Value = _comment.Title;
            Parms[1].Value = _comment.Comments;
            Parms[2].Value = _comment.Comments1;
            Parms[3].Value = _comment.Comments2;
            Parms[4].Value = _comment.Author;
            Parms[5].Value =  new Guid(_comment.AuthorId);
            Parms[6].Value = _comment.GoodCounter;
            Parms[7].Value = _comment.BadCounter;
            Parms[8].Value = new Guid(_comment.RegardingObjectId);
            Parms[9].Value = _comment.RegardingObjectIdName;
            Parms[10].Value = _comment.RegardingObjectTypeCode;
            Parms[11].Value = new Guid(_comment.CreatedBy);
            Parms[12].Value = new Guid(_comment.ModifyBy);
            Parms[13].Value = _comment.StatusCode; 
            Parms[14].Direction = ParameterDirection.Output;
            Parms[15].Direction = ParameterDirection.ReturnValue;

            result = "";
            int status = 0;
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, "CommentBase_Create", Parms);
                status = ((Parms[15].Value != null) ? ((int)Parms[15].Value) : -1);
                result = status.ToString();
                if (Common.Utils.IsGuid(Parms[14].Value.ToString()))
                    _comment.CommentId = Parms[14].Value.ToString();

                result += "|" + _comment.CommentId;
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            } 
        }
        /// <summary>
        /// 修改评论
        /// </summary>
        public void ModifyComment(Model.CommentBase _comment, out string result)
        {
            Parms = new SqlParameter[] {
					new SqlParameter("@Title", SqlDbType.NVarChar, 512),
					new SqlParameter("@Comments", SqlDbType.NVarChar, 4000),
					new SqlParameter("@Comments1", SqlDbType.NVarChar, 4000),
					new SqlParameter("@Comments2", SqlDbType.NVarChar, 4000),
					new SqlParameter("@Author", SqlDbType.VarChar, 256),
					new SqlParameter("@AuthorId", SqlDbType.UniqueIdentifier),
					new SqlParameter("@GoodCounter", SqlDbType.Int),
					new SqlParameter("@BadCounter", SqlDbType.Int),  
					new SqlParameter("@ModifyBy", SqlDbType.UniqueIdentifier),
					new SqlParameter("@StatusCode", SqlDbType.Int),
                    new SqlParameter("@CommentId",SqlDbType.UniqueIdentifier),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
            };
            Parms[0].Value = _comment.Title;
            Parms[1].Value = _comment.Comments;
            Parms[2].Value = _comment.Comments1;
            Parms[3].Value = _comment.Comments2;
            Parms[4].Value = _comment.Author;
            Parms[5].Value = new Guid(_comment.AuthorId);
            Parms[6].Value = _comment.GoodCounter;
            Parms[7].Value = _comment.BadCounter;
            Parms[8].Value = new Guid(_comment.ModifyBy);
            Parms[9].Value = _comment.StatusCode;
            Parms[10].Value = new Guid(_comment.CommentId);
            Parms[11].Direction = ParameterDirection.ReturnValue;

            result = "";
            int status = 0;
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, "CommentBase_Update", Parms);
                status = ((Parms[11].Value != null) ? ((int)Parms[11].Value) : -1);
                if (status == 0)
                {
                    result = "succeeded";
                    result += "|" + _comment.CommentId;
                }
                else if (status == -1)
                {
                    result = "存储过程执行错误!"; ///storedprocedure error
                }
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            } 
        }
        /// <summary>
        /// 审核评论
        /// </summary>
        public void ApproveComment(string commentid, string approvedby, int statuscode, out string result)
        {
            StringBuilder strSql = new StringBuilder();

            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@CommentId", SqlDbType.UniqueIdentifier, 16), 
                new SqlParameter("@ApprovedBy", SqlDbType.UniqueIdentifier, 16),
                new SqlParameter("@StatusCode", SqlDbType.Int), 
            };
            parameters[0].Value = new Guid(commentid);
            parameters[1].Value = new Guid(approvedby);
            parameters[2].Value = statuscode;

            strSql.Append("UPDATE CommentBase SET ApprovedBy = @ApprovedBy, StatusCode = @StatusCode, ApprovedOn = Getdate() WHERE [CommentId] = @CommentId");

            result = ""; 
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.Text, strSql.ToString(), parameters);

                result = "succeeded";
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            } 
        }
        /// <summary>
        /// 删除评论
        /// </summary>
        public void Delete(string commentid, out string result)
        {
            StringBuilder strSql = new StringBuilder();

            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@CommentId", SqlDbType.UniqueIdentifier, 16)
            };
            parameters[0].Value = new Guid(commentid); 

            strSql.Append("DELETE FROM CommentBase WHERE [CommentId] = @CommentId");

            result = "";
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.Text, strSql.ToString(), parameters);

                result = "succeeded";
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            } 
        }

        /// <summary>
        /// 查询评论
        /// </summary>
        /// <returns></returns>
        public Model.CommentBase GetObjectById(string commentId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM CommentBase WHERE CommentId = @CommentId ");

            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@CommentId", SqlDbType.UniqueIdentifier) };
            parameters[0].Value = new Guid(commentId);

            Model.CommentBase _commentObj = null;
            try
            {
                SqlDataReader rd = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
                if (rd.Read())
                {
                    _commentObj = new Model.CommentBase();
                    _commentObj.CommentId = rd["CommentId"].ToString();
                    _commentObj.Title = rd["Title"].ToString();
                    _commentObj.Comments = rd["Comments"].ToString();
                    _commentObj.Comments1 = rd["Comments1"].ToString();
                    _commentObj.Comments2 = rd["Comments2"].ToString();
                    _commentObj.Author = rd["Author"].ToString();
                    _commentObj.AuthorId = rd["AuthorId"].ToString(); 
                    _commentObj.GoodCounter = int.Parse(rd["GoodCounter"].ToString()); 
                    _commentObj.BadCounter = int.Parse(rd["BadCounter"].ToString()); 
                    _commentObj.RegardingObjectId = rd["RegardingObjectId"].ToString();
                    _commentObj.RegardingObjectIdName = rd["RegardingObjectIdName"].ToString(); 
                    _commentObj.RegardingObjectTypeCode = int.Parse(rd["RegardingObjectTypeCode"].ToString()); 
                    _commentObj.CreatedOn = DateTime.Parse(rd["CreatedOn"].ToString()); 
                    _commentObj.CreatedBy = rd["CreatedBy"].ToString(); 
                    _commentObj.ModifyOn = DateTime.Parse(rd["ModifyOn"].ToString()); 
                    _commentObj.ModifyBy = rd["ModifyBy"].ToString(); 
                    _commentObj.StatusCode = int.Parse(rd["StatusCode"].ToString()); 
                }
            }
            catch
            {
                return null;
            }
            return _commentObj;
        }

        /// <summary>
        /// 更新品论信息
        /// </summary>
        public void UpdateInfoByParms()
        {
            
        }

        /// <summary>
        /// 回复评论
        /// </summary>
        public void CreateReview(Model.CommentReviewBase _comment, out string result)
        {
            Parms = new SqlParameter[] {  
					new SqlParameter("@Content", SqlDbType.NVarChar, 4000), 
					new SqlParameter("@Author", SqlDbType.VarChar, 256),
					new SqlParameter("@AuthorId", SqlDbType.UniqueIdentifier), 
                    new SqlParameter("@CommentId",SqlDbType.UniqueIdentifier),
                    new SqlParameter("@StatusCode", SqlDbType.Int),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
            };
            Parms[0].Value = _comment.Content;
            Parms[1].Value = _comment.Author;  
            Parms[2].Value = new Guid(_comment.AuthorId);
            Parms[3].Value = new Guid(_comment.CommentId);
            Parms[4].Value = _comment.StatusCode;  
            Parms[5].Direction = ParameterDirection.ReturnValue;

            result = "";
            int status = 0;
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, "CommentReview_Create", Parms);
                status = ((Parms[5].Value != null) ? ((int)Parms[5].Value) : -1);
                if (status == 0)
                {
                    result = "succeeded";  
                }
                else if (status == -1)
                {
                    result = "存储过程执行错误!"; ///storedprocedure error
                }
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            } 
        
        }
        /// <summary>
        /// 修改回复
        /// </summary>
        public void ModfiyReview(Model.CommentReviewBase _comment, out string result)
        {
            Parms = new SqlParameter[] {  
					new SqlParameter("@Content", SqlDbType.NVarChar, 4000),  
                    new SqlParameter("@CommentReviewId",SqlDbType.UniqueIdentifier),
                    new SqlParameter("@StatusCode", SqlDbType.Int),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
            };
            Parms[0].Value = _comment.Content;
            Parms[1].Value = new Guid(_comment.CommentReviewId); 
            Parms[2].Value = _comment.StatusCode;
            Parms[3].Direction = ParameterDirection.ReturnValue;

            result = "";
            int status = 0;
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, "CommentReview_Update", Parms);
                status = ((Parms[3].Value != null) ? ((int)Parms[3].Value) : -1);
                if (status == 0)
                {
                    result = "succeeded";
                }
                else if (status == -1)
                {
                    result = "存储过程执行错误!"; ///storedprocedure error
                }
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            } 
        
        }
        /// <summary>
        /// 审核回复
        /// </summary>
        public void ApproveReview(string reviewId, int statuscode, out string result)
        {
            StringBuilder strSql = new StringBuilder();

            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@CommentReviewId", SqlDbType.UniqueIdentifier, 16),  
                new SqlParameter("@StatusCode", SqlDbType.Int), 
            };
            parameters[0].Value = new Guid(reviewId); 
            parameters[1].Value = statuscode;

            strSql.Append("UPDATE CommentReviewBase SET StatusCode = @StatusCode WHERE [CommentReviewId] = @CommentReviewId");

            result = "";
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.Text, strSql.ToString(), Parms);

                result = "succeeded";
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            } 
        }
        /// <summary>
        /// 删除回复
        /// </summary>
        public void DeleteReview(string reviewId, out string result)
        {
            StringBuilder strSql = new StringBuilder();

            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@CommentReviewId", SqlDbType.UniqueIdentifier, 16)
            };
            parameters[0].Value = new Guid(reviewId);

            strSql.Append("DELETE FROM CommentReviewBase WHERE [CommentReviewId] = @CommentReviewId");

            result = "";
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.Text, strSql.ToString(), Parms);

                result = "succeeded";
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
        }

        /// <summary>
        /// 查询回复记录
        /// </summary>
        /// <param name="commentId"></param>
        /// <returns></returns>
        public IList<Model.CommentReviewBase> GetReviewList(string commentId)
        {
            return null;
        }
    }
}
